﻿<?xml version="1.0" encoding="utf-8" ?>
<root>
    <!-- Parameters
        {本周一}
        {本周日}
        {本月1日}
        {今年1日}
        {昨天}
        {上周一}
        {上周日}
        {上月1日}
        {去年1日}
        {去年同期}
        {去年周同期}
        {本周五}
        {上周五}
        -->
    <Parameters>
        <Parameter src="args">
            <Name>当前时间天</Name>
            <Sql>本参数固定不可变，其它参数使用C# Format格式中的 {0} 引用此参数</Sql>
        </Parameter>
        <Parameter src="sql">
            <Name>本周一</Name>
            <Sql>
                <![CDATA[
                select to_char(trunc(to_date('{0:yyyyMMdd}','yyyymmdd'),'iw'),'yyyy-mm-dd') a from dual
            ]]>
            </Sql>
        </Parameter>
        <Parameter src="sql">
            <Name>本周日</Name>
            <Sql>
                <![CDATA[
                select to_char(trunc(to_date('{0:yyyyMMdd}','yyyymmdd'),'iw')+6,'yyyy-mm-dd') a from dual
            ]]></Sql>
        </Parameter>
        <Parameter src="sql">
            <Name>本月1日</Name>
            <Sql>
                <![CDATA[
                select to_char(trunc(to_date('{0:yyyyMMdd}','yyyymmdd'),'mm'),'yyyy-mm-dd') a from dual
            ]]>
            </Sql>
        </Parameter>
        <Parameter src="sql">
            <Name>今年1日</Name>
            <Sql>
                <![CDATA[
                select to_char(trunc(to_date('{0:yyyyMMdd}','yyyymmdd'),'yyyy'),'yyyy-mm-dd') a from dual
            ]]>
            </Sql>
        </Parameter>
        <Parameter src="sql">
            <Name>昨天</Name>
            <Sql>
                <![CDATA[
                select to_char(to_date('{0:yyyyMMdd}','yyyymmdd')-1,'yyyy-mm-dd') a from dual
            ]]>
            </Sql>
        </Parameter>
        <Parameter src="sql">
            <Name>上周一</Name>
            <Sql>
                <![CDATA[
                select to_char(trunc(to_date('{0:yyyyMMdd}','yyyymmdd'),'iw')-7,'yyyy-mm-dd') a from dual
            ]]>
            </Sql>
        </Parameter>
        <Parameter src="sql">
            <Name>上周日</Name>
            <Sql>
                <![CDATA[
                select to_char(trunc(to_date('{0:yyyyMMdd}','yyyymmdd'),'iw')-1,'yyyy-mm-dd') a from dual
            ]]>
            </Sql>
        </Parameter>
        <Parameter src="sql">
            <Name>上月1日</Name>
            <Sql>
                <![CDATA[
                select to_char(trunc(trunc(to_date('{0:yyyyMMdd}','yyyymmdd'),'mm')-1,'mm'),'yyyy-mm-dd') a from dual
            ]]>
            </Sql>
        </Parameter>
        <Parameter src="sql">
            <Name>去年1日</Name>
            <Sql>
                <![CDATA[
                select to_char(trunc(trunc(to_date('{0:yyyyMMdd}','yyyymmdd'),'yyyy')-1,'yyyy'),'yyyy-mm-dd') a from dual
            ]]>
            </Sql>
        </Parameter>
        <Parameter src="sql">
            <Name>去年同期</Name>
            <Sql>
                <![CDATA[
                select to_char(add_months(trunc(to_date('{0:yyyyMMdd}','yyyymmdd'),'iw')+6, -12), 'yyyy-mm-dd') a from dual
            ]]>
            </Sql>
        </Parameter>
        <Parameter src="sql">
            <Name>去年周同期</Name>
            <Sql>
                <!--to_date('20130415', 'yyyymmdd')-->
                <![CDATA[
select days
  from (select days
          from (select trunc(trunc(to_date('{0:yyyyMMdd}','yyyymmdd'),'yyyy')-1,'yyyy') + rownum - 1 days
                  from dba_objects
                 where rownum <= 366) a
         where extract(year from days) =
               extract(year from trunc(trunc(to_date('{0:yyyyMMdd}','yyyymmdd'),'yyyy')-1,'yyyy'))
           and to_char(days, 'iw') =
               to_char(to_date('{0:yyyyMMdd}', 'yyyymmdd'), 'iw')
         order by 1 asc) t
 where rownum = 1
                ]]>
            </Sql>
        </Parameter>
        <Parameter src="sql">
            <Name>本周五</Name>
            <Sql>
                <![CDATA[
                select to_char(trunc(to_date('{0:yyyyMMdd}','yyyymmdd'),'iw')+4,'yyyy-mm-dd') a from dual
            ]]>
            </Sql>
        </Parameter>
        <Parameter src="sql">
            <Name>上周五</Name>
            <Sql>
                <![CDATA[
                select to_char(trunc(trunc(to_date('{0:yyyyMMdd}', 'yyyymmdd'), 'iw') - 1, 'iw') + 4, 'yyyy-mm-dd') a from dual
            ]]>
            </Sql>
        </Parameter>
    </Parameters>
    <FileName>
        <Sql>
        <![CDATA[
        select 'SampleWeekly_'||'{本周一}'||'.docx' from dual
        ]]>
        </Sql>
    </FileName>
    <Place>
        <PlaceHolder Type="NonRecursive">
            <Name>标题起始月日</Name>
            <Sql>
                <![CDATA[
select to_char(to_date({本周一}, 'yyyymmdd'), 'mm') || '月' ||
       to_char(to_date({本周一}, 'yyyymmdd'), 'dd') || '日'
  from dual
        ]]>
            </Sql>
        </PlaceHolder>
        <PlaceHolder Type="NonRecursive">
            <Name>标题终止月日</Name>
            <Sql>
                <![CDATA[
select to_char(to_date({本周日}, 'yyyymmdd'), 'mm') || '月' ||
       to_char(to_date({本周日}, 'yyyymmdd'), 'dd') || '日'
  from dual
        ]]>
            </Sql>
        </PlaceHolder>
        <PlaceHolder Type="NonRecursive">
            <Name>CreateDate</Name>
            <Sql>
                <![CDATA[
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual
        ]]>
            </Sql>
        </PlaceHolder>
        <PlaceHolder Type="Recursive">
            <Name>samplerow</Name>
            <Columns>
              col1, col2,col3.col4,col5,col6
            </Columns>
            <Sql>
                <![CDATA[
select rownum, rownum, rownum, rownum, rownum, rownum
  from dual 
  connect by level <= 10
        ]]>
            </Sql>
        </PlaceHolder>
    </Place>

    <Charts>
        <Chart type="Default">
            <Name>linechart</Name>
            <Series>b</Series>
            <xAxis>d</xAxis>
            <yAxis>a</yAxis>
            <Sql>
                <![CDATA[
select extract(year from event_time) b,
       to_char(event_time, 'mm-dd') d,
       round(test, 0) a
  from table
 where event_time between
       trunc(trunc(to_date({本周日}, 'yyyymmdd'), 'yyyy') - 1, 'yyyy') and
       to_date({本周日}, 'yyyymmdd')
 order by event_time
        ]]>
            </Sql>
        </Chart>
        <Chart type="Manual">
            <Name>
                SampleManual
            </Name>
            <Series>b</Series>
            <xAxis>d</xAxis>
            <yAxis>a</yAxis>
            <Sql>
                <![CDATA[
SELECT decode(r_n, 2, 'col1', 3, 'col2', 1, 'col3') b,
       eventdate d,
       decode(r_n, 1, col1, 2, col2, 3, col3) a
  FROM table t1,
       (select rownum r_n from dual connect by level <= 3) t2
        ]]>
            </Sql>
        </Chart>
    </Charts>
</root>
